﻿using System.Data;

namespace PolePosition.v3.EntityFactory
{
    public sealed class GridDataFactory
    {
        #region Project Data for Grids

        public static DataSet GetXmissionGpsProjectPoleData(int projectId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorVisitXMissionStructurePole_SelectForGrid";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXmissionGpsProjectPoleExcelData(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorVisitXMissionStructurePole_SelectForExcel";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXmissionGpsProjectStructureData(int projectId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorVisitXMissionStructure_SelectForGrid";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (searchBy != Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXmissionInspectionProjectPoleData(int projectId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorInspVisitXMissionStructurePole_SelectForGrid";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXmissionInspectionProjectPoleExcelData(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorInspVisitXMissionStructurePole_SelectForExcel";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXmissionInspectionProjectStructureData(int projectId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorInspVisitXMissionStructure_SelectForGrid";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetDistributionInspectionProjectData(int projectId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorDistributionPole_SelectForGrid";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetDistributionInspectionProjectExcelData(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorDistributionPole_SelectForExcel";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);
         
            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        #endregion

        #region Asset Data for Grids

        public static DataSet GetXmissionAssetData(int utilityId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetXMissionStructuresPoles_SelectForGrid";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXmissionAssetExcelData(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetXMissionStructuresPoles_SelectForExcel";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetDistributionAssetData(int utilityId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetDistribution_SelectForGrid";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }
       
        public static DataSet GetDistributionAssetExcelData(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetDistribution_SelectForExcel";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetMeterAssetData(int utilityId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetMeters_SelectForGrid";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetTransformerAssetData(int vendorProjectId, int? pageSize, int? pageNumber, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetTransformers_SelectForGrid";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityId";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = vendorProjectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetMeterAssetExcelData(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetMeters_SelectForExcel";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetTransformerAssetExcelData(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetTransformers_SelectForExcel";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }


        #endregion

        #region Project Id List for KML from Grids

        public static string GetXmissionGpsProjectPoleIdList(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorVisitXMissionStructurePole_SelectIdList";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        public static string GetXmissionGpsProjectStructureIdList(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorVisitXMissionStructure_SelectIdList";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        public static string GetXmissionInspectionProjectPoleIdList(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorInspVisitXMissionStructurePole_SelectIdList";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        public static string GetXmissionInspectionProjectStructureIdList(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorInspVisitXMissionStructure_SelectIdList";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        public static string GetDistributionInspectionProjectIdList(int projectId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorDistributionPole_SelectIdList";

            //add param Vendor_ProjectFK
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@Vendor_ProjectFK";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        #endregion

        #region Asset Id List for KML from Grids

        public static string GetXmissionAssetIdList(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetXMissionStructures_SelectIdList";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);


            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        public static string GetDistributionAssetIdList(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetDistribution_SelectIdList";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);

            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        public static string GetMeterAssetIdList(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetMeters_SelectIdList";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);



            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }


        public static string GetTransformerAssetIdList(int utilityId, Common.Enumerations.GridSearchByTpes searchBy, string searchBytext)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetTransformers_SelectIdList";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);



            if (searchBy != PolePosition.v3.Common.Enumerations.GridSearchByTpes.None && !string.IsNullOrEmpty(searchBytext))
            {
                //add param SearchByType
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByType";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBy.ToString();
                SqlCmd.Parameters.Add(SqlParam);

                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@SearchByText";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = searchBytext;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteScalar<string>(SqlCmd);
        }

        #endregion

        #region Project Id List for visits to Assets Grids

        public static DataSet GetDistributionMeterToAssetData(int projectId, int? pageSize, int? pageNumber, int? feets)
        {
            // build command
            var SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetDistribution_SelectForMeterToAssetGrid";

            //add param UtilityFk
            var SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@projectFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (feets.HasValue)
            {
                //add param feets
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@feets";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = feets.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetDistributionInspVisitToAssetData(int projectId, int? pageSize, int? pageNumber, int? feets)
        {
            // build command
            var SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_UtilityAssetDistribution_SelectForInspToAssetGrid";

            //add param UtilityFk
            var SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@projectFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (feets.HasValue)
            {
                //add param feets
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@feets";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = feets.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXMissionInspVisitToAssetData(int projectId, int? pageSize, int? pageNumber, int? feets)
        {
            // build command
            var SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorInspVisitXMissionStructurePole_SelectForInspToAssetGrid";

            //add param UtilityFk
            var SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@projectFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (feets.HasValue)
            {
                //add param feets
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@feets";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = feets.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        public static DataSet GetXMissionVisitToAssetData(int projectId, int? pageSize, int? pageNumber, int? feets)
        {
            // build command
            var SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_VendorVisitXMissionStructurePole_SelectForVisitToAssetGrid";

            //add param UtilityFk
            var SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@projectFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = projectId;
            SqlCmd.Parameters.Add(SqlParam);

            if (pageSize.HasValue)
            {
                //add param pagesize
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageSize";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageSize.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            if (pageNumber.HasValue)
            {
                //add param pagenumber
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.Int;
                SqlParam.ParameterName = "@PageNumber";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = pageNumber.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }


            if (feets.HasValue)
            {
                //add param feets
                SqlParam = new System.Data.SqlClient.SqlParameter();
                SqlParam.SqlDbType = SqlDbType.VarChar;
                SqlParam.ParameterName = "@feets";
                SqlParam.Direction = ParameterDirection.Input;
                SqlParam.Value = feets.Value;
                SqlCmd.Parameters.Add(SqlParam);
            }

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }

        #endregion

        public static DataSet GetWorkQueueData(int utilityId)
        {
            // build command
            System.Data.SqlClient.SqlCommand SqlCmd;
            SqlCmd = new System.Data.SqlClient.SqlCommand();
            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
            SqlCmd.CommandText = "up_WorkQueue";

            //add param UtilityFk
            System.Data.SqlClient.SqlParameter SqlParam;
            SqlParam = new System.Data.SqlClient.SqlParameter();
            SqlParam.SqlDbType = SqlDbType.Int;
            SqlParam.ParameterName = "@UtilityFk";
            SqlParam.Direction = ParameterDirection.Input;
            SqlParam.Value = utilityId;
            SqlCmd.Parameters.Add(SqlParam);

            // execute
            return ProjectCommon2.Helpers.SqlHelper.ExecuteDataSet(SqlCmd);
        }
    }
}
